package com.aquila.bible.sqlite;

import java.util.ArrayList;

import com.aquila.bible.ReadHistoryObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class ReadHistorySqlite extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "ReadHistorySqlite.db";
	public static final String TABLE_NAME = "ReadHistorySqlite_TB_NAME";
	public static final int Version =1;
	private SQLiteDatabase db;
	
	private final String  KEY_ID = "id" ;
	private final String  KEY_BOOK_TITLE = "bookTitle" ;
	private final String  KEY_BOOK_INDEX = "bookIndex" ;
	private final String  KEY_CHAPTER_INDEX = "chapterIndex" ;
	private final String  KEY_IS_NEW_TESTAMENT = "isNewTestament" ;
	private final String  KEY_TOTAL_CHAPTER = "totalChapter" ;
	private Context context ;
	public ReadHistorySqlite(Context context) {
		super(context, DATABASE_NAME, null, Version);
		this.context = context ;
		db = getWritableDatabase() ;
		createTable(db) ;
		
	}

	
	/***
	 * 创建数据库表
	 * @param db
	 *2012-7-30 下午1:15:53
	 */
	private void createTable(SQLiteDatabase db){
		String sqlstr = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "+
				KEY_ID +" INTEGER PRIMARY KEY, "+
	            KEY_BOOK_TITLE + "  VARCHAR2(20), "+
	            KEY_BOOK_INDEX +" INTEGER, "+
	            KEY_CHAPTER_INDEX + " INTEGER, "+
	            KEY_IS_NEW_TESTAMENT + "  INTEGER, "+
	            KEY_TOTAL_CHAPTER + " INTEGER);";
		try{
			db.execSQL(sqlstr);
		}catch (SQLException e) {
			e.printStackTrace() ;
		}
	}
	
	/**
	 * 插入记录
	 * @param object
	 *2012-7-30 下午1:42:34
	 */
	public void insertData(ReadHistoryObject object){
		if (object == null) {
			return ;
		}
//		try {
			ContentValues values = new ContentValues() ;
			values.put(KEY_BOOK_TITLE, object.getBookTitle()) ;
			values.put(KEY_BOOK_INDEX, object.getBookIndex()) ;
			values.put(KEY_CHAPTER_INDEX, object.getChapterIndex()) ;
			values.put(KEY_IS_NEW_TESTAMENT, object.getIsNewTestament());
			values.put(KEY_TOTAL_CHAPTER, object.getTotalChapter()) ;
			db.insert(TABLE_NAME, null, values) ;
			
//		} catch (Exception e) {
//			e.printStackTrace() ;
//			
//		}
			
	}
	
	/**
	 * 删除一条记录
	 * @param object
	 *2012-7-30 下午1:47:31
	 */
	public void deleteOneData(ReadHistoryObject object){
		if (object == null) {
			return ;
		}
		
		if (object.getId() > 0) {
			db.delete(TABLE_NAME, KEY_ID + " = " +object.getId(), null) ;
		}
		else {
		Toast.makeText(context, "待处理", Toast.LENGTH_SHORT).show() ;	
		}
		
	}
	
	
	/**
	 * 获取一条记录
	 * @param bookIndex
	 * @param chapterIndex
	 * @return
	 *2012-7-30 下午2:12:39
	 */
	public ReadHistoryObject getOneData(int bookIndex, int chapterIndex){
		String sqlString = "select " + KEY_BOOK_INDEX + " from" + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sqlString, null) ;
		if (cursor == null || cursor.getCount() == 0) {
			return null ;
		}
		ReadHistoryObject object = null;	
		while (cursor.moveToNext()) {
			if (chapterIndex != cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) {
				continue ;
			}
			object = new ReadHistoryObject() ;
			object.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID))) ;
			object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_BOOK_INDEX))) ;
			object.setBookTitle(cursor.getString(cursor.getColumnIndex(KEY_BOOK_TITLE))) ;
			object.setChapterIndex(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) ;
			object.setIsNewTestament(cursor.getInt(cursor.getColumnIndex(KEY_IS_NEW_TESTAMENT))) ;
			object.setTotalChapter(cursor.getInt(cursor.getColumnIndex(KEY_TOTAL_CHAPTER))) ;
		}
		cursor.close() ;
		return object ;
	}
	
	
	
	/**
	 * 读取所有记录
	 * @return
	 *2012-7-30 下午2:05:22
	 */
	public ArrayList<ReadHistoryObject> getAllDatas(){
		ArrayList<ReadHistoryObject> arrayList = new ArrayList<ReadHistoryObject>() ;
		
		String sql = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sql, null) ;
		cursor.moveToFirst() ;
		while(cursor.moveToNext()){
			ReadHistoryObject object = new ReadHistoryObject() ;
			
			object.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID))) ;
			object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_BOOK_INDEX))) ;
			object.setBookTitle(cursor.getString(cursor.getColumnIndex(KEY_BOOK_TITLE))) ;
			object.setChapterIndex(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) ;
			object.setIsNewTestament(cursor.getInt(cursor.getColumnIndex(KEY_IS_NEW_TESTAMENT))) ;
			object.setTotalChapter(cursor.getInt(cursor.getColumnIndex(KEY_TOTAL_CHAPTER))) ;
			
			arrayList.add(object) ;
		}
		cursor.close() ;
		
		return arrayList ;
		
	}
	
	
	
	
	
	
	/**
	 * 获取保存历史的记录
	 * @return
	 *2012-7-30 下午1:57:52
	 */
	public int getDataCount(){
		String sqlString = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sqlString, null) ;
		return cursor.getCount() ;
	}
	
	
	
	
	
	/**
	 * 更新记录
	 * @param object
	 *2012-7-30 下午1:54:07
	 */
	public void updateData(ReadHistoryObject object){
		if (object == null) {
			return ;
		}
		
		ContentValues values = new ContentValues() ;
		values.put(KEY_ID, object.getId()) ;
		
		values.put(KEY_BOOK_TITLE, object.getBookTitle()) ;
		values.put(KEY_BOOK_INDEX, object.getBookIndex()) ;
		values.put(KEY_CHAPTER_INDEX, object.getChapterIndex()) ;
		values.put(KEY_IS_NEW_TESTAMENT, object.getIsNewTestament());
		values.put(KEY_TOTAL_CHAPTER, object.getTotalChapter()) ;
		db.insert(TABLE_NAME, null, values) ;
		
		db.update(TABLE_NAME, values, KEY_ID + " = " + object.getId(), null) ;
	}
	
	
	
	
	
	/***
	 * 打开数据库
	 *2012-7-30 下午1:37:13
	 */
	public void open() {
		if (db == null || db.isOpen()) {
			db = getWritableDatabase() ;
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close(){
		if (db.isOpen()) {
			db.close() ;
		}
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	@Override
	public void onCreate(SQLiteDatabase db) {
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}
	
	
	
	
	
	
	
	
	
	
	
}
